package com.matt.repository.impl;

import com.matt.foundation.constant.ClassesConstant;
import com.matt.foundation.query.ChildrenQuery;
import com.matt.foundation.vo.IDCountVO;
import com.matt.foundation.vo.children.ChildrenBaseInfoVo;
import com.matt.foundation.vo.children.WebChildrenVo;
import com.matt.repository.base.BaseNativeSqlRepository;
import com.matt.repository.base.ChildrenNativeRepository;
import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * *******************************
 * Created by lch.
 * form com.matt
 * on on 2018/11/20
 * *******************************
 */
@Service
public class ChildrenNativeRepositoryImpl extends BaseNativeSqlRepository implements ChildrenNativeRepository {



    @Override
    public List<WebChildrenVo> findByQueryNew(ChildrenQuery query,boolean paged) {
        String querySql = "";
        //1.全部
        if (query.getCategory() == 0) {
            querySql += "SELECT id,name,createTime,month,earlyTotalCourseNum,earlyLeftCourseNum,birthday,status,nurseryStatus,classesId," +
                    "   clssesName,classesCategory,CONCAT('[',GROUP_CONCAT(t.simpleClassObj SEPARATOR ','),']') AS simpleClassObjs FROM " +
                    "    (SELECT " +
                    "        ch.id AS id,ch.`name` AS name,ch.`month` AS `month`, ch.`createTime` AS `createTime`,cei.earlyTotalCourseNum AS earlyTotalCourseNum," +
                    "        cei.earlyLeftCourseNum AS earlyLeftCourseNum,ch.birthday AS birthday,ch.`status` AS status," +
                    "        cei.nurseryStatus AS nurseryStatus,a.classesId AS classesId,b.`name` AS clssesName,b.category AS classesCategory," +
                    "        CONCAT('{', '\"id\":\"', a.classesId, '\",\"name\":\"', b.name, '\",\"category\":\"', b.category, '\"}') AS simpleClassObj" +
                    "    FROM children AS ch" +
                    "    INNER JOIN children_extend_info AS cei ON ch.id = cei.childrenId" +
                    "    LEFT JOIN rel_classes_children a ON ch.id = a.childrenId" +
                    "    LEFT JOIN classes b ON a.classesId = b.id" +
                    String.format("    WHERE ch.aid = '%s'", query.getAreaId());

            //班级Id
            if(StringUtils.isNotBlank(query.getClassId())){
                querySql += String.format(" and a.classesId='%s'", query.getClassId());
            }

            //宝宝状态查询
            if (query.getStatus() != null && query.getStatus() != -1)
                querySql += String.format(" and ch.status='%s'", query.getStatus());

            //宝宝名字模糊查询
            if (StringUtils.isNotBlank(query.getName()))
                querySql += String.format(" and ch.name like concat('%s','%s','%s')", "%", query.getName(), "%");

            querySql += "   ) t ";
            querySql += "   GROUP BY t.id ORDER BY t.createTime DESC";
        }
        //2.托班
        if (query.getCategory() == 1) {
            //托班状态 已结束
            if (query.getNurseryStatus() != null && query.getNurseryStatus() == 0) {
                querySql += String.format("select ch.id as id,ch.`name` as name,ch.`month` as `month`,ch.status as status,cei.nurseryStatus as nurseryStatus," +
                        "cei.nurseryEndDate as nurseryEndDate,ch.birthday as birthday" +
                        " from children as ch inner join children_extend_info as cei on ch.id=cei.childrenId " +
                        " where cei.nurseryStatus=0 and ch.aid='%s'",query.getAreaId());
                //宝宝名字模糊查询
                if (StringUtils.isNotBlank(query.getName()))
                    querySql += String.format(" and ch.name like concat('%s','%s','%s')", "%", query.getName(), "%");

                querySql += " group by ch.id";
                querySql += " order by ch.createTime desc ";
            }
            //托班进行中
            if (query.getNurseryStatus() == null || query.getNurseryStatus() == 1) {
                String addSql = "";
                if (StringUtils.isNotBlank(query.getName()))
                    addSql += " and c.name like concat('%','"+query.getName()+"','%')";
                querySql += "SELECT t1.childrenId as id,name as name ,nurseryStatus as nurseryStatus,nurseryEndDate as nurseryEndDate,status as status,month as month,nurseryClassName,nurseryClassId,t1.birthday as birthday" +
                        " from (SELECT c.id as childrenId,c.`name` as name, cei.nurseryStatus as nurseryStatus ,cei.nurseryEndDate as nurseryEndDate,c.aid as areaId,c.createTime as createTime,c.status as status,c.month as month,c.birthday as birthday " +
                        " from children c, children_extend_info cei WHERE c.id = cei.childrenId "+addSql+" and cei.nurseryStatus>0) t1 LEFT JOIN (select rel.childrenId,c.id as nurseryClassId,c.name as nurseryClassName " +
                        " from rel_classes_children rel LEFT JOIN classes c on c.id = rel.classesId WHERE c.category =1 ) t2 " +
                        " on t1.childrenId = t2.childrenId WHERE t1.areaId='"+query.getAreaId()+"'";

                //班级Id
                if(StringUtils.isNotBlank(query.getClassId())){
                    querySql += String.format(" and c.id='%s'", query.getClassId());
                }

                querySql += " group by id";
                querySql += " order by t1.createTime desc ";
            }

        }
        //3.早教
        if (query.getCategory() == 2) {
            //早教状态 0:已结束
            if (query.getEarlyStatus() != null && query.getEarlyStatus() == 0) {
                querySql += String.format("select ch.id as id,ch.`name` as name,ch.`month` as `month`,cei.earlyTotalCourseNum as earlyTotalCourseNum,cei.earlyLeftCourseNum as earlyLeftCourseNum,ch.`status` as status,ch.birthday as birthday " +
                        " from children as ch inner join children_extend_info as cei on ch.id=cei.childrenId where cei.earlyLeftCourseNum=0 and ch.aid='%s'",query.getAreaId());
                //宝宝名字模糊查询
                if (StringUtils.isNotBlank(query.getName()))
                    querySql += String.format(" and ch.name like concat('%s','%s','%s')", "%", query.getName(), "%");

                querySql += " group by ch.id";
                querySql += " order by ch.createTime desc ";
            }
            //早教状态  1：进行中
            if (query.getEarlyStatus() == null || query.getEarlyStatus() == 1) {
                String addSql = "";
                if (StringUtils.isNotBlank(query.getName()))
                    addSql +=" and c.name like concat('%','"+query.getName()+"','%')";

                querySql +="SELECT t1.childrenId as id,name as name ,earlyTotalCourseNum as earlyTotalCourseNum,earlyLeftCourseNum as earlyLeftCourseNum,status as status,month as month,earlyClassName as earlyClassName,earlyClassId as earlyClassId,t1.birthday as birthday " +
                        " from (SELECT c.id as childrenId,c.`name` as name, cei.earlyTotalCourseNum as earlyTotalCourseNum ,cei.earlyLeftCourseNum as earlyLeftCourseNum,c.aid as areaId,c.createTime as createTime,c.status as status,c.month as month,c.birthday as birthday " +
                        " from children c, children_extend_info cei WHERE c.id = cei.childrenId "+addSql+" and cei.earlyLeftCourseNum>0) t1 LEFT JOIN (select rel.childrenId,c.id as earlyClassId,c.name as earlyClassName " +
                        " from rel_classes_children rel LEFT JOIN classes c on c.id = rel.classesId WHERE c.category =2 ) t2 " +
                        " on t1.childrenId = t2.childrenId WHERE t1.areaId='"+query.getAreaId()+"'";

                //班级Id
                if(StringUtils.isNotBlank(query.getClassId())){
                    querySql += String.format(" and c.id='%s'", query.getClassId());
                }

                querySql += " group by id";
                querySql += " order by t1.createTime desc ";
            }
        }

        if(paged){
            querySql += String.format(" limit %s, %s ", query.getPageNum() * query.getPageSize(), query.getPageSize());
        }

        List<WebChildrenVo> list = sqlObjectList(querySql, WebChildrenVo.class);
        return list;
    }






//    @Override
//    public List<WebChildrenVo> findByQueryNew(ChildrenQuery query, boolean paged) {
//        String querySql = "";
//        // 1. 全部
//        if (query.getCategory() == 0) {
//            querySql += "SELECT ANY_VALUE(id) AS id, ANY_VALUE(name) AS name, ANY_VALUE(createTime) AS createTime, "
//                    + "CAST(ANY_VALUE(month) AS UNSIGNED) AS month, "  // 强制转换为整数类型
//                    + "CAST(ANY_VALUE(earlyTotalCourseNum) AS UNSIGNED) AS earlyTotalCourseNum, "
//                    + "CAST(ANY_VALUE(earlyLeftCourseNum) AS UNSIGNED) AS earlyLeftCourseNum, "
//                    + "ANY_VALUE(birthday) AS birthday, "
//                    + "CAST(ANY_VALUE(status) AS UNSIGNED) AS status, "
//                    + "CAST(ANY_VALUE(nurseryStatus) AS UNSIGNED) AS nurseryStatus, "
//                    + "ANY_VALUE(classesId) AS classesId, ANY_VALUE(clssesName) AS clssesName, "
//                    + "CAST(ANY_VALUE(classesCategory) AS UNSIGNED) AS classesCategory, "
//                    + "CONCAT('[',GROUP_CONCAT(t.simpleClassObj SEPARATOR ','),']') AS simpleClassObjs FROM "
//                    + "(SELECT ch.id AS id, ch.`name` AS name, "
//                    + "CAST(ch.`month` AS UNSIGNED) AS `month`, "  // 强制转换
//                    + "ch.`createTime` AS `createTime`, "
//                    + "CAST(cei.earlyTotalCourseNum AS UNSIGNED) AS earlyTotalCourseNum, "
//                    + "CAST(cei.earlyLeftCourseNum AS UNSIGNED) AS earlyLeftCourseNum, "
//                    + "ch.birthday AS birthday, "
//                    + "CAST(ch.`status` AS UNSIGNED) AS status, "
//                    + "CAST(cei.nurseryStatus AS UNSIGNED) AS nurseryStatus, "
//                    + "a.classesId AS classesId, b.`name` AS clssesName, "
//                    + "CAST(b.category AS UNSIGNED) AS classesCategory, "
//                    + "CONCAT('{', '\"id\":\"', a.classesId, '\",\"name\":\"', b.name, '\",\"category\":\"', b.category, '\"}') AS simpleClassObj "
//                    + "FROM children AS ch "
//                    + "INNER JOIN children_extend_info AS cei ON ch.id = cei.childrenId "
//                    + "LEFT JOIN rel_classes_children a ON ch.id = a.childrenId "
//                    + "LEFT JOIN classes b ON a.classesId = b.id "
//                    + "WHERE ch.aid = '" + query.getAreaId() + "'";
//
//            // 班级Id
//            if (StringUtils.isNotBlank(query.getClassId())) {
//                querySql += " AND a.classesId = '" + query.getClassId() + "'";
//            }
//
//            // 宝宝状态查询
//            if (query.getStatus() != null && query.getStatus() != -1) {
//                querySql += " AND ch.status = " + query.getStatus();
//            }
//
//            // 宝宝名字模糊查询
//            if (StringUtils.isNotBlank(query.getName())) {
//                querySql += " AND ch.name LIKE '%" + query.getName() + "%'";
//            }
//
//            querySql += ") t ";
//            querySql += "GROUP BY t.id ORDER BY t.createTime DESC";
//        }
//        // 2. 托班
//        else if (query.getCategory() == 1) {
//            // 托班状态 已结束
//            if (query.getNurseryStatus() != null && query.getNurseryStatus() == 0) {
//                querySql += "SELECT ANY_VALUE(id) AS id, ANY_VALUE(name) AS name, "
//                        + "CAST(ANY_VALUE(month) AS UNSIGNED) AS month, "  // 强制转换
//                        + "CAST(ANY_VALUE(status) AS UNSIGNED) AS status, "
//                        + "CAST(ANY_VALUE(nurseryStatus) AS UNSIGNED) AS nurseryStatus, "
//                        + "ANY_VALUE(nurseryEndDate) AS nurseryEndDate, ANY_VALUE(birthday) AS birthday "
//                        + "FROM children AS ch "
//                        + "INNER JOIN children_extend_info AS cei ON ch.id = cei.childrenId "
//                        + "WHERE cei.nurseryStatus = 0 AND ch.aid = '" + query.getAreaId() + "'";
//
//                // 宝宝名字模糊查询
//                if (StringUtils.isNotBlank(query.getName())) {
//                    querySql += " AND ch.name LIKE '%" + query.getName() + "%'";
//                }
//
//                querySql += " GROUP BY ch.id";
//                querySql += " ORDER BY ch.createTime DESC";
//            }
//            // 托班进行中
//            else if (query.getNurseryStatus() == null || query.getNurseryStatus() == 1) {
//                String addSql = "";
//                if (StringUtils.isNotBlank(query.getName())) {
//                    addSql += " AND c.name LIKE '%" + query.getName() + "%'";
//                }
//
//                querySql += "SELECT ANY_VALUE(t1.childrenId) AS id, ANY_VALUE(name) AS name, "
//                        + "CAST(ANY_VALUE(nurseryStatus) AS UNSIGNED) AS nurseryStatus, "
//                        + "ANY_VALUE(nurseryEndDate) AS nurseryEndDate, "
//                        + "CAST(ANY_VALUE(status) AS UNSIGNED) AS status, "
//                        + "CAST(ANY_VALUE(month) AS UNSIGNED) AS month, "  // 强制转换
//                        + "ANY_VALUE(nurseryClassName) AS nurseryClassName, ANY_VALUE(nurseryClassId) AS nurseryClassId, "
//                        + "ANY_VALUE(t1.birthday) AS birthday "
//                        + "FROM (SELECT c.id AS childrenId, c.`name` AS name, "
//                        + "CAST(cei.nurseryStatus AS UNSIGNED) AS nurseryStatus, "
//                        + "cei.nurseryEndDate AS nurseryEndDate, c.aid AS areaId, c.createTime AS createTime, "
//                        + "CAST(c.status AS UNSIGNED) AS status, "
//                        + "CAST(c.month AS UNSIGNED) AS month, "  // 强制转换
//                        + "c.birthday AS birthday "
//                        + "FROM children c INNER JOIN children_extend_info cei ON c.id = cei.childrenId "
//                        + "WHERE cei.nurseryStatus > 0" + addSql + ") t1 "
//                        + "LEFT JOIN (SELECT rel.childrenId, c.id AS nurseryClassId, c.name AS nurseryClassName "
//                        + "FROM rel_classes_children rel LEFT JOIN classes c ON c.id = rel.classesId WHERE c.category = 1) t2 "
//                        + "ON t1.childrenId = t2.childrenId WHERE t1.areaId = '" + query.getAreaId() + "'";
//
//                // 班级Id
//                if (StringUtils.isNotBlank(query.getClassId())) {
//                    querySql += " AND c.id = '" + query.getClassId() + "'";
//                }
//
//                querySql += " GROUP BY id";
//                querySql += " ORDER BY t1.createTime DESC";
//            }
//        }
//        // 3. 早教
//        else if (query.getCategory() == 2) {
//            // 早教状态 0:已结束
//            if (query.getEarlyStatus() != null && query.getEarlyStatus() == 0) {
//                querySql += "SELECT ANY_VALUE(id) AS id, ANY_VALUE(name) AS name, "
//                        + "CAST(ANY_VALUE(month) AS UNSIGNED) AS month, "  // 强制转换
//                        + "CAST(ANY_VALUE(earlyTotalCourseNum) AS UNSIGNED) AS earlyTotalCourseNum, "
//                        + "CAST(ANY_VALUE(earlyLeftCourseNum) AS UNSIGNED) AS earlyLeftCourseNum, "
//                        + "CAST(ANY_VALUE(status) AS UNSIGNED) AS status, "
//                        + "ANY_VALUE(birthday) AS birthday "
//                        + "FROM children AS ch INNER JOIN children_extend_info AS cei ON ch.id = cei.childrenId "
//                        + "WHERE cei.earlyLeftCourseNum = 0 AND ch.aid = '" + query.getAreaId() + "'";
//
//                // 宝宝名字模糊查询
//                if (StringUtils.isNotBlank(query.getName())) {
//                    querySql += " AND ch.name LIKE '%" + query.getName() + "%'";
//                }
//
//                querySql += " GROUP BY ch.id";
//                querySql += " ORDER BY ch.createTime DESC";
//            }
//            // 早教状态  1：进行中
//            else if (query.getEarlyStatus() == null || query.getEarlyStatus() == 1) {
//                String addSql = "";
//                if (StringUtils.isNotBlank(query.getName())) {
//                    addSql += " AND c.name LIKE '%" + query.getName() + "%'";
//                }
//
//                querySql += "SELECT ANY_VALUE(t1.childrenId) AS id, ANY_VALUE(name) AS name, "
//                        + "CAST(ANY_VALUE(earlyTotalCourseNum) AS UNSIGNED) AS earlyTotalCourseNum, "
//                        + "CAST(ANY_VALUE(earlyLeftCourseNum) AS UNSIGNED) AS earlyLeftCourseNum, "
//                        + "CAST(ANY_VALUE(status) AS UNSIGNED) AS status, "
//                        + "CAST(ANY_VALUE(month) AS UNSIGNED) AS month, "  // 强制转换
//                        + "ANY_VALUE(earlyClassName) AS earlyClassName, ANY_VALUE(earlyClassId) AS earlyClassId, "
//                        + "ANY_VALUE(t1.birthday) AS birthday "
//                        + "FROM (SELECT c.id AS childrenId, c.`name` AS name, "
//                        + "CAST(cei.earlyTotalCourseNum AS UNSIGNED) AS earlyTotalCourseNum, "
//                        + "CAST(cei.earlyLeftCourseNum AS UNSIGNED) AS earlyLeftCourseNum, "
//                        + "c.aid AS areaId, c.createTime AS createTime, "
//                        + "CAST(c.status AS UNSIGNED) AS status, "
//                        + "CAST(c.month AS UNSIGNED) AS month, "  // 强制转换
//                        + "c.birthday AS birthday "
//                        + "FROM children c INNER JOIN children_extend_info cei ON c.id = cei.childrenId "
//                        + "WHERE cei.earlyLeftCourseNum > 0" + addSql + ") t1 "
//                        + "LEFT JOIN (SELECT rel.childrenId, c.id AS earlyClassId, c.name AS earlyClassName "
//                        + "FROM rel_classes_children rel LEFT JOIN classes c ON c.id = rel.classesId WHERE c.category = 2) t2 "
//                        + "ON t1.childrenId = t2.childrenId WHERE t1.areaId = '" + query.getAreaId() + "'";
//
//                // 班级Id
//                if (StringUtils.isNotBlank(query.getClassId())) {
//                    querySql += " AND c.id = '" + query.getClassId() + "'";
//                }
//
//                querySql += " GROUP BY id";
//                querySql += " ORDER BY t1.createTime DESC";
//            }
//        }
//
//        if (paged) {
//            querySql += " LIMIT " + (query.getPageNum() * query.getPageSize()) + ", " + query.getPageSize();
//        }
//
//        List<WebChildrenVo> list = sqlObjectList(querySql, WebChildrenVo.class);
//        return list;
//    }
//    public List<WebChildrenVo> findByQueryNew(ChildrenQuery query,boolean paged) {
//        String querySql = "";
//        //1.全部
//        if (query.getCategory() == 0) {
//            querySql += "SELECT ANY_VALUE(id) AS id, ANY_VALUE(name) AS name, ANY_VALUE(createTime) AS createTime, "
//                    + "ANY_VALUE(month) AS month, ANY_VALUE(earlyTotalCourseNum) AS earlyTotalCourseNum, "
//                    + "ANY_VALUE(earlyLeftCourseNum) AS earlyLeftCourseNum, ANY_VALUE(birthday) AS birthday, "
//                    + "ANY_VALUE(status) AS status, ANY_VALUE(nurseryStatus) AS nurseryStatus, "
//                    + "ANY_VALUE(classesId) AS classesId, ANY_VALUE(clssesName) AS clssesName, "
//                    + "ANY_VALUE(classesCategory) AS classesCategory, "
//                    + "CONCAT('[',GROUP_CONCAT(t.simpleClassObj SEPARATOR ','),']') AS simpleClassObjs FROM "
//                    + "(SELECT ch.id AS id, ch.`name` AS name, ch.`month` AS `month`, ch.`createTime` AS `createTime`, "
//                    + "cei.earlyTotalCourseNum AS earlyTotalCourseNum, cei.earlyLeftCourseNum AS earlyLeftCourseNum, "
//                    + "ch.birthday AS birthday, ch.`status` AS status, cei.nurseryStatus AS nurseryStatus, "
//                    + "a.classesId AS classesId, b.`name` AS clssesName, b.category AS classesCategory, "
//                    + "CONCAT('{', '\"id\":\"', a.classesId, '\",\"name\":\"', b.name, '\",\"category\":\"', b.category, '\"}') AS simpleClassObj "
//                    + "FROM children AS ch "
//                    + "INNER JOIN children_extend_info AS cei ON ch.id = cei.childrenId "
//                    + "LEFT JOIN rel_classes_children a ON ch.id = a.childrenId "
//                    + "LEFT JOIN classes b ON a.classesId = b.id "
//                    + "WHERE ch.aid = '" + query.getAreaId() + "'";
//
//            //班级Id
//            if(StringUtils.isNotBlank(query.getClassId())){
//                querySql += " AND a.classesId = '" + query.getClassId() + "'";
//            }
//
//            //宝宝状态查询
//            if (query.getStatus() != null && query.getStatus() != -1) {
//                querySql += " AND ch.status = " + query.getStatus();
//            }
//
//            //宝宝名字模糊查询
//            if (StringUtils.isNotBlank(query.getName())) {
//                querySql += " AND ch.name LIKE '%" + query.getName() + "%'";
//            }
//
//            querySql += ") t ";
//            querySql += "GROUP BY t.id ORDER BY t.createTime DESC";
//        }
//        //2.托班
//        else if (query.getCategory() == 1) {
//            //托班状态 已结束
//            if (query.getNurseryStatus() != null && query.getNurseryStatus() == 0) {
//                querySql += "SELECT ANY_VALUE(id) AS id, ANY_VALUE(name) AS name, ANY_VALUE(month) AS month, "
//                        + "ANY_VALUE(status) AS status, ANY_VALUE(nurseryStatus) AS nurseryStatus, "
//                        + "ANY_VALUE(nurseryEndDate) AS nurseryEndDate, ANY_VALUE(birthday) AS birthday "
//                        + "FROM children AS ch "
//                        + "INNER JOIN children_extend_info AS cei ON ch.id = cei.childrenId "
//                        + "WHERE cei.nurseryStatus = 0 AND ch.aid = '" + query.getAreaId() + "'";
//
//                //宝宝名字模糊查询
//                if (StringUtils.isNotBlank(query.getName())) {
//                    querySql += " AND ch.name LIKE '%" + query.getName() + "%'";
//                }
//
//                querySql += " GROUP BY ch.id";
//                querySql += " ORDER BY ch.createTime DESC";
//            }
//            //托班进行中
//            else if (query.getNurseryStatus() == null || query.getNurseryStatus() == 1) {
//                String addSql = "";
//                if (StringUtils.isNotBlank(query.getName())) {
//                    addSql += " AND c.name LIKE '%" + query.getName() + "%'";
//                }
//
//                querySql += "SELECT ANY_VALUE(t1.childrenId) AS id, ANY_VALUE(name) AS name, ANY_VALUE(nurseryStatus) AS nurseryStatus, "
//                        + "ANY_VALUE(nurseryEndDate) AS nurseryEndDate, ANY_VALUE(status) AS status, ANY_VALUE(month) AS month, "
//                        + "ANY_VALUE(nurseryClassName) AS nurseryClassName, ANY_VALUE(nurseryClassId) AS nurseryClassId, ANY_VALUE(t1.birthday) AS birthday "
//                        + "FROM (SELECT c.id AS childrenId, c.`name` AS name, cei.nurseryStatus AS nurseryStatus, "
//                        + "cei.nurseryEndDate AS nurseryEndDate, c.aid AS areaId, c.createTime AS createTime, "
//                        + "c.status AS status, c.month AS month, c.birthday AS birthday "
//                        + "FROM children c INNER JOIN children_extend_info cei ON c.id = cei.childrenId "
//                        + "WHERE cei.nurseryStatus > 0" + addSql + ") t1 "
//                        + "LEFT JOIN (SELECT rel.childrenId, c.id AS nurseryClassId, c.name AS nurseryClassName "
//                        + "FROM rel_classes_children rel LEFT JOIN classes c ON c.id = rel.classesId WHERE c.category = 1) t2 "
//                        + "ON t1.childrenId = t2.childrenId WHERE t1.areaId = '" + query.getAreaId() + "'";
//
//                //班级Id
//                if(StringUtils.isNotBlank(query.getClassId())){
//                    querySql += " AND c.id = '" + query.getClassId() + "'";
//                }
//
//                querySql += " GROUP BY id";
//                querySql += " ORDER BY t1.createTime DESC";
//            }
//        }
//        //3.早教
//        else if (query.getCategory() == 2) {
//            //早教状态 0:已结束
//            if (query.getEarlyStatus() != null && query.getEarlyStatus() == 0) {
//                querySql += "SELECT ANY_VALUE(id) AS id, ANY_VALUE(name) AS name, ANY_VALUE(month) AS month, "
//                        + "ANY_VALUE(earlyTotalCourseNum) AS earlyTotalCourseNum, ANY_VALUE(earlyLeftCourseNum) AS earlyLeftCourseNum, "
//                        + "ANY_VALUE(status) AS status, ANY_VALUE(birthday) AS birthday "
//                        + "FROM children AS ch INNER JOIN children_extend_info AS cei ON ch.id = cei.childrenId "
//                        + "WHERE cei.earlyLeftCourseNum = 0 AND ch.aid = '" + query.getAreaId() + "'";
//
//                //宝宝名字模糊查询
//                if (StringUtils.isNotBlank(query.getName())) {
//                    querySql += " AND ch.name LIKE '%" + query.getName() + "%'";
//                }
//
//                querySql += " GROUP BY ch.id";
//                querySql += " ORDER BY ch.createTime DESC";
//            }
//            //早教状态  1：进行中
//            else if (query.getEarlyStatus() == null || query.getEarlyStatus() == 1) {
//                String addSql = "";
//                if (StringUtils.isNotBlank(query.getName())) {
//                    addSql += " AND c.name LIKE '%" + query.getName() + "%'";
//                }
//
//                querySql += "SELECT ANY_VALUE(t1.childrenId) AS id, ANY_VALUE(name) AS name, ANY_VALUE(earlyTotalCourseNum) AS earlyTotalCourseNum, "
//                        + "ANY_VALUE(earlyLeftCourseNum) AS earlyLeftCourseNum, ANY_VALUE(status) AS status, ANY_VALUE(month) AS month, "
//                        + "ANY_VALUE(earlyClassName) AS earlyClassName, ANY_VALUE(earlyClassId) AS earlyClassId, ANY_VALUE(t1.birthday) AS birthday "
//                        + "FROM (SELECT c.id AS childrenId, c.`name` AS name, cei.earlyTotalCourseNum AS earlyTotalCourseNum, "
//                        + "cei.earlyLeftCourseNum AS earlyLeftCourseNum, c.aid AS areaId, c.createTime AS createTime, "
//                        + "c.status AS status, c.month AS month, c.birthday AS birthday "
//                        + "FROM children c INNER JOIN children_extend_info cei ON c.id = cei.childrenId "
//                        + "WHERE cei.earlyLeftCourseNum > 0" + addSql + ") t1 "
//                        + "LEFT JOIN (SELECT rel.childrenId, c.id AS earlyClassId, c.name AS earlyClassName "
//                        + "FROM rel_classes_children rel LEFT JOIN classes c ON c.id = rel.classesId WHERE c.category = 2) t2 "
//                        + "ON t1.childrenId = t2.childrenId WHERE t1.areaId = '" + query.getAreaId() + "'";
//
//                //班级Id
//                if(StringUtils.isNotBlank(query.getClassId())){
//                    querySql += " AND c.id = '" + query.getClassId() + "'";
//                }
//
//                querySql += " GROUP BY id";
//                querySql += " ORDER BY t1.createTime DESC";
//            }
//        }
//
//        if(paged){
//            querySql += " LIMIT " + (query.getPageNum() * query.getPageSize()) + ", " + query.getPageSize();
//        }
//
//        List<WebChildrenVo> list = sqlObjectList(querySql, WebChildrenVo.class);
//        return list;
//    }



    @Override
    public Long findCountByQueryNew(ChildrenQuery query){
        String querySql = "";
        //1.全部
        if (query.getCategory() == 0) {
            querySql += String.format("SELECT ch.id as id from children as ch inner join children_extend_info as cei " +
                    "on ch.id=cei.childrenId LEFT JOIN rel_classes_children a ON ch.id = a.childrenId LEFT JOIN classes b ON a.classesId = b.id " +
                    "where ch.aid='%s'",query.getAreaId());

            //班级Id
            if(StringUtils.isNotBlank(query.getClassId())){
                querySql += String.format(" and a.classesId='%s'", query.getClassId());
            }

            //宝宝状态查询
            if (query.getStatus() != null && query.getStatus() != -1)
                querySql += String.format(" and ch.status='%s'", query.getStatus());
            //宝宝名字模糊查询
            if (StringUtils.isNotBlank(query.getName()))
                querySql += String.format(" and ch.name like concat('%s','%s','%s')", "%", query.getName(), "%");

            querySql += " group by ch.id";
        }
        //2.托班
        if (query.getCategory() == 1) {
            //托班状态 已结束
            if (query.getNurseryStatus() != null && query.getNurseryStatus() == 0) {
                querySql += String.format("select ch.id as id from children as ch inner join children_extend_info as cei on ch.id=cei.childrenId " +
                        " where cei.nurseryStatus=0 and ch.aid='%s'",query.getAreaId());
                //宝宝名字模糊查询
                if (StringUtils.isNotBlank(query.getName()))
                    querySql += String.format(" and ch.name like concat('%s','%s','%s')", "%", query.getName(), "%");

                querySql += " group by ch.id";
            }
            //托班进行中
            if (query.getNurseryStatus() == null || query.getNurseryStatus() == 1) {
                String addSql = "";
                if (StringUtils.isNotBlank(query.getName()))
                    addSql += " and c.name like concat('%','"+query.getName()+"','%')";
                querySql += "SELECT t1.childrenId as id,name as name ,nurseryStatus as nurseryStatus,nurseryEndDate as nurseryEndDate,status as status,month as month,nurseryClassName,nurseryClassId,t1.birthday as birthday" +
                        " from (SELECT c.id as childrenId,c.`name` as name, cei.nurseryStatus as nurseryStatus ,cei.nurseryEndDate as nurseryEndDate,c.aid as areaId,c.createTime as createTime,c.status as status,c.month as month,c.birthday as birthday " +
                        " from children c, children_extend_info cei WHERE c.id = cei.childrenId "+addSql+" and cei.nurseryStatus>0) t1 LEFT JOIN (select rel.childrenId,c.id as nurseryClassId,c.name as nurseryClassName " +
                        " from rel_classes_children rel LEFT JOIN classes c on c.id = rel.classesId WHERE c.category =1 ) t2 " +
                        " on t1.childrenId = t2.childrenId WHERE t1.areaId='"+query.getAreaId()+"'";

                //班级Id
                if(StringUtils.isNotBlank(query.getClassId())){
                    querySql += String.format(" and c.id='%s'", query.getClassId());
                }

                querySql += " group by id";
            }

        }
        //3.早教
        if (query.getCategory() == 2) {
            //早教状态 0:已结束
            if (query.getEarlyStatus() != null && query.getEarlyStatus() == 0) {
                querySql += String.format("select ch.id as id " +
                        " from children as ch inner join children_extend_info as cei on ch.id=cei.childrenId where cei.earlyLeftCourseNum=0 and ch.aid='%s'",query.getAreaId());
                //宝宝名字模糊查询
                if (StringUtils.isNotBlank(query.getName()))
                    querySql += String.format(" and ch.name like concat('%s','%s','%s')", "%", query.getName(), "%");

                querySql += " group by ch.id";
            }
            //早教状态  1：进行中
            if (query.getEarlyStatus() == null || query.getEarlyStatus() == 1) {
                String addSql = "";
                if (StringUtils.isNotBlank(query.getName()))
                    addSql +=" and c.name like concat('%','"+query.getName()+"','%')";

                querySql +="SELECT t1.childrenId as id,name as name ,earlyTotalCourseNum as earlyTotalCourseNum,earlyLeftCourseNum as earlyLeftCourseNum,status as status,month as month,earlyClassName as earlyClassName,earlyClassId as earlyClassId,t1.birthday as birthday " +
                        " from (SELECT c.id as childrenId,c.`name` as name, cei.earlyTotalCourseNum as earlyTotalCourseNum ,cei.earlyLeftCourseNum as earlyLeftCourseNum,c.aid as areaId,c.createTime as createTime,c.status as status,c.month as month,c.birthday as birthday " +
                        " from children c, children_extend_info cei WHERE c.id = cei.childrenId "+addSql+" and cei.earlyLeftCourseNum>0) t1 LEFT JOIN (select rel.childrenId,c.id as earlyClassId,c.name as earlyClassName " +
                        " from rel_classes_children rel LEFT JOIN classes c on c.id = rel.classesId WHERE c.category =2 ) t2 " +
                        " on t1.childrenId = t2.childrenId WHERE t1.areaId='"+query.getAreaId()+"'";

                //班级Id
                if(StringUtils.isNotBlank(query.getClassId())){
                    querySql += String.format(" and c.id='%s'", query.getClassId());
                }

                querySql += " group by id";
            }
        }

        querySql = String.format("select count(*) from (%s) d", querySql);

        System.out.println("count query sql:"+querySql);

        Long ret = sqlSingleResult(querySql);

        return ret;
    }


    @Override
    public List<WebChildrenVo> findByQuery(ChildrenQuery query) {
        String querySql = "";
        //1.全部
        if (query.getCategory() == 0) {
            querySql += String.format("SELECT ch.id as id,ch.`name` as name,ch.`month` as `month`,cei.earlyTotalCourseNum as earlyTotalCourseNum,cei.earlyLeftCourseNum as earlyLeftCourseNum,ch.birthday as birthday," +
                    " (SELECT DISTINCT(cl.`name`) from classes as cl INNER JOIN rel_classes_children as rcc on cl.id=rcc.classesId WHERE rcc.childrenId=ch.id and cl.category=1) as nurseryClassName," +
                    " (SELECT DISTINCT(cl.`id`) from classes as cl INNER JOIN rel_classes_children as rcc on cl.id=rcc.classesId WHERE rcc.childrenId=ch.id and cl.category=1) as nurseryClassId, " +
                    " (SELECT DISTINCT(cl.`name`) from classes as cl INNER JOIN rel_classes_children as rcc on cl.id=rcc.classesId WHERE rcc.childrenId=ch.id and cl.category=2 ) as earlyClassName," +
                    " (SELECT DISTINCT(cl.`id`) from classes as cl INNER JOIN rel_classes_children as rcc on cl.id=rcc.classesId WHERE rcc.childrenId=ch.id and cl.category=2 ) as earlyClassId," +
                    "  ch.`status` as status,cei.nurseryStatus as nurseryStatus " +
                    " FROM children as ch inner join children_extend_info as cei on ch.id=cei.childrenId  WHERE true and ch.aid='%s'",query.getAreaId());
            //宝宝状态查询
            if (query.getStatus() != null && query.getStatus() != -1)
                querySql += String.format(" and ch.status='%s'", query.getStatus());
            //宝宝名字模糊查询
            if (StringUtils.isNotBlank(query.getName()))
                querySql += String.format(" and ch.name like concat('%s','%s','%s')", "%", query.getName(), "%");

            querySql += " order by ch.createTime desc ";
        }
        //2.托班
        if (query.getCategory() == 1) {
            //托班状态 已结束
            if (query.getNurseryStatus() != null && query.getNurseryStatus() == 0) {
                querySql += String.format("select ch.id as id,ch.`name` as name,ch.`month` as `month`,ch.status as status,cei.nurseryStatus as nurseryStatus,cei.nurseryEndDate as nurseryEndDate,ch.birthday as birthday" +
                        " from children as ch inner join children_extend_info as cei on ch.id=cei.childrenId " +
                        " where cei.nurseryStatus=0 and ch.aid='%s'",query.getAreaId());
                //宝宝名字模糊查询
                if (StringUtils.isNotBlank(query.getName()))
                    querySql += String.format(" and ch.name like concat('%s','%s','%s')", "%", query.getName(), "%");

                querySql += " order by ch.createTime desc ";
            }
            //托班进行中
            if (query.getNurseryStatus() != null && query.getNurseryStatus() == 1) {
                String addSql = "";
                if (StringUtils.isNotBlank(query.getName()))
                    addSql +=" and c.name like concat('%','"+query.getName()+"','%')";
                querySql +="SELECT t1.childrenId as id,name as name ,nurseryStatus as nurseryStatus,nurseryEndDate as nurseryEndDate,status as status,month as month,nurseryClassName,nurseryClassId,t1.birthday as birthday" +
                        " from (SELECT c.id as childrenId,c.`name` as name, cei.nurseryStatus as nurseryStatus ,cei.nurseryEndDate as nurseryEndDate,c.aid as areaId,c.createTime as createTime,c.status as status,c.month as month,c.birthday as birthday " +
                        " from children c, children_extend_info cei WHERE c.id = cei.childrenId "+addSql+" and cei.nurseryStatus>0) t1 LEFT JOIN (select rel.childrenId,c.id as nurseryClassId,c.name as nurseryClassName " +
                        " from rel_classes_children rel LEFT JOIN classes c on c.id = rel.classesId WHERE c.category =1 ) t2 " +
                        " on t1.childrenId = t2.childrenId WHERE t1.areaId='"+query.getAreaId()+"'";
                querySql += " order by t1.createTime desc ";
            }

        }
        //3.早教
        if (query.getCategory() == 2) {
            //早教状态 0:已结束
            if (query.getEarlyStatus() != null && query.getEarlyStatus() == 0) {
                querySql += String.format("select ch.id as id,ch.`name` as name,ch.`month` as `month`,cei.earlyTotalCourseNum as earlyTotalCourseNum,cei.earlyLeftCourseNum as earlyLeftCourseNum,ch.`status` as status,ch.birthday as birthday " +
                        " from children as ch inner join children_extend_info as cei on ch.id=cei.childrenId where cei.earlyLeftCourseNum=0 and ch.aid='%s'",query.getAreaId());
                //宝宝名字模糊查询
                if (StringUtils.isNotBlank(query.getName()))
                    querySql += String.format(" and ch.name like concat('%s','%s','%s')", "%", query.getName(), "%");

                querySql += " order by ch.createTime desc ";
            }
            //早教状态  1：进行中
            if (query.getEarlyStatus() != null && query.getEarlyStatus() == 1) {
               String addSql = "";
               if (StringUtils.isNotBlank(query.getName()))
                   addSql +=" and c.name like concat('%','"+query.getName()+"','%')";

               querySql +="SELECT t1.childrenId as id,name as name ,earlyTotalCourseNum as earlyTotalCourseNum,earlyLeftCourseNum as earlyLeftCourseNum,status as status,month as month,earlyClassName as earlyClassName,earlyClassId as earlyClassId,t1.birthday as birthday " +
                       " from (SELECT c.id as childrenId,c.`name` as name, cei.earlyTotalCourseNum as earlyTotalCourseNum ,cei.earlyLeftCourseNum as earlyLeftCourseNum,c.aid as areaId,c.createTime as createTime,c.status as status,c.month as month,c.birthday as birthday " +
                       " from children c, children_extend_info cei WHERE c.id = cei.childrenId "+addSql+" and cei.earlyLeftCourseNum>0) t1 LEFT JOIN (select rel.childrenId,c.id as earlyClassId,c.name as earlyClassName " +
                       " from rel_classes_children rel LEFT JOIN classes c on c.id = rel.classesId WHERE c.category =2 ) t2 " +
                       " on t1.childrenId = t2.childrenId WHERE t1.areaId='"+query.getAreaId()+"'";
                querySql += " order by t1.createTime desc ";
            }
        }

            querySql += String.format(" limit %s, %s ", query.getPageNum() * query.getPageSize(), query.getPageSize());
             //querySql +="limit "+query.getPageNum() * query.getPageSize() +","+query.getPageSize();

            List<WebChildrenVo> list = sqlObjectList(querySql, WebChildrenVo.class);
            return list;
        }

    @Override
    public Long findCountByQuery(ChildrenQuery query) {
        String querySql = "";
        //1.全部
        if (query.getCategory() == 0) {
            querySql += String.format("select count(*) from(SELECT count(*)" +
                    " from children as ch inner join children_extend_info as cei " +
                    "on ch.id=cei.childrenId LEFT JOIN rel_classes_children a ON ch.id = a.childrenId LEFT JOIN classes b ON a.classesId = b.id " +
                    "where ch.aid='%s'",query.getAreaId());
            //宝宝状态查询
            if (query.getStatus() != null && query.getStatus() != -1)
                querySql += String.format(" and ch.status='%s'", query.getStatus());
            //宝宝名字模糊查询
            if (StringUtils.isNotBlank(query.getName()))
                querySql += String.format(" and ch.name like concat('%s','%s','%s')", "%", query.getName(), "%");

            querySql += " group by ch.id) as d";
        }
        //2.托班
        if (query.getCategory() == 1) {
            //托班状态 已结束
            if (query.getNurseryStatus() != null && query.getNurseryStatus() == 0) {
                querySql += String.format("select count(*) from (select count(*) " +
                        " from children as ch inner join children_extend_info as cei on ch.id=cei.childrenId " +
                        " where cei.nurseryStatus=0 and ch.aid='%s'",query.getAreaId());
                //宝宝名字模糊查询
                if (StringUtils.isNotBlank(query.getName()))
                    querySql += String.format(" and ch.name like concat('%s','%s','%s')", "%", query.getName(), "%");

                querySql += " group by t1.childrenId) as d";
            }
            //托班进行中
            if (query.getNurseryStatus() == null || query.getNurseryStatus() == 1) {
                String addSql = "";
                if (StringUtils.isNotBlank(query.getName()))
                    addSql += " and c.name like concat('%','"+query.getName()+"','%')";
                querySql += "select count(*) from (SELECT count(t1.childrenId) " +
                        " from (SELECT c.id as childrenId,c.`name` as name, cei.nurseryStatus as nurseryStatus ,cei.nurseryEndDate as nurseryEndDate,c.aid as areaId,c.createTime as createTime,c.status as status,c.month as month,c.birthday as birthday " +
                        " from children c, children_extend_info cei WHERE c.id = cei.childrenId "+addSql+" and cei.nurseryStatus>0) t1 LEFT JOIN (select rel.childrenId,c.id as nurseryClassId,c.name as nurseryClassName " +
                        " from rel_classes_children rel LEFT JOIN classes c on c.id = rel.classesId WHERE c.category =1 ) t2 " +
                        " on t1.childrenId = t2.childrenId WHERE t1.areaId='"+query.getAreaId()+"'";
                querySql += " group by t1.childrenId) as d";
            }

        }
        //3.早教
        if (query.getCategory() == 2) {
            //早教状态 0:已结束
            if (query.getEarlyStatus() != null && query.getEarlyStatus() == 0) {
                querySql += String.format("select count(*) from (select count(*) " +
                        " from children as ch inner join children_extend_info as cei on ch.id=cei.childrenId where cei.earlyLeftCourseNum=0 and ch.aid='%s'",query.getAreaId());
                //宝宝名字模糊查询
                if (StringUtils.isNotBlank(query.getName()))
                    querySql += String.format(" and ch.name like concat('%s','%s','%s')", "%", query.getName(), "%");

                querySql += " group by ch.id) as d";
            }
            //早教状态  1：进行中
            if (query.getEarlyStatus() == null || query.getEarlyStatus() == 1) {
                String addSql = "";
                if (StringUtils.isNotBlank(query.getName()))
                    addSql +=" and c.name like concat('%','"+query.getName()+"','%')";

                querySql +="select count(*) from (SELECT count(t1.childrenId) " +
                        " from (SELECT c.id as childrenId,c.`name` as name, cei.earlyTotalCourseNum as earlyTotalCourseNum ,cei.earlyLeftCourseNum as earlyLeftCourseNum,c.aid as areaId,c.createTime as createTime,c.status as status,c.month as month,c.birthday as birthday " +
                        " from children c, children_extend_info cei WHERE c.id = cei.childrenId "+addSql+" and cei.earlyLeftCourseNum>0) t1 LEFT JOIN (select rel.childrenId,c.id as earlyClassId,c.name as earlyClassName " +
                        " from rel_classes_children rel LEFT JOIN classes c on c.id = rel.classesId WHERE c.category =2 ) t2 " +
                        " on t1.childrenId = t2.childrenId WHERE t1.areaId='"+query.getAreaId()+"'";

                querySql += " group by t1.childrenId) as d";
            }
        }

        //querySql += String.format(" limit %s, %s ", query.getPageNum() * query.getPageSize(), query.getPageSize());
        //querySql +="limit "+query.getPageNum() * query.getPageSize() +","+query.getPageSize();


        System.out.println("111:"+querySql);
        //List<WebChildrenVo> list = sqlObjectList(querySql, WebChildrenVo.class);
        //return list;
        List objectArrayList = sqlSingleList(querySql);
        return Long.valueOf(objectArrayList.get(0).toString());
    }

    @Override
    public List<ChildrenBaseInfoVo> getByClassesId(String classesId) {
        String querySql = String.format("select ch.id as childId,ch.name as childName,ch.imgUrl as childHeadUrl " +
                " from children as ch inner join rel_classes_children as rcc on ch.id=rcc.childrenId inner join classes as cl on cl.id=rcc.classesId " +
                " where rcc.classesId ='%s' and cl.category='%s'",classesId, ClassesConstant.Category.NURSERY.codeOf());
        List<ChildrenBaseInfoVo> list = sqlObjectList(querySql,ChildrenBaseInfoVo.class);
        return list;
    }



	@Override
	public int findEarlyPlanCourseNumByChildrenId(String childrenId) {
		 String findChildrenSql = String.format("select count(*) from children_course_item cci where cci.childrenId='%s' and cci.status=1 ",
         childrenId);
       // Long number = sqlArrayList(findChildrenSql);
        List objectArrayList = sqlSingleList(findChildrenSql);
        return Integer.valueOf(objectArrayList.get(0).toString());
	}

    @Override
    public Map<String, Integer> findEarlyPlanCourseNumByChildrenIds(List<String> childrenIds){
        if(childrenIds == null || childrenIds.size() == 0){
            return new HashMap<>();
        }

        String findChildrenSql = "select cci.childrenId as 'id', count(*) as 'count'" +
                        "from children_course_item cci " +
                        "where cci.status=1 and cci.childrenId in ( :1 ) " +
                        "group by cci.childrenId";


        List params = new ArrayList();
        params.add(childrenIds);

        List<IDCountVO> objectArrayList = sqlObjectList(findChildrenSql, params, IDCountVO.class);

        return IDCountVO.toMap(objectArrayList);
    }
}
